package com.idroid.database;

import java.util.ArrayList;
import java.util.List;

import com.idroid.domain.AreaType;
import com.idroid.domain.ParkingArea;
import com.idroid.domain.PaymentMethodType;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class ParkingFlexDataSource {

	// Database fields
	private SQLiteDatabase database;
	private ParkingFlexDbHelper dbHelper;

	/*
	 * private String[] allColumns = { MySQLiteHelper.COLUMN_ID,
	 * MySQLiteHelper.COLUMN_NAME
	 * ,MySQLiteHelper.COLUMN_TITLE,MySQLiteHelper.COLUMN_PHONE,
	 * MySQLiteHelper.COLUMN_EMAIL, MySQLiteHelper.COLUMN_TWID};
	 */

	public ParkingFlexDataSource(Context context) {
		dbHelper = new ParkingFlexDbHelper(context);
	}

	public void open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}

	public void close() {
		dbHelper.close();
	}

	public ArrayList<ParkingArea> getAreasByCityState(String city,
			String state, boolean maxPriceFilterEnabled, double maxPriceFilter,
			boolean areaTypeFilterEnabled, AreaType areaFilter) {
		ArrayList<ParkingArea> areas = new ArrayList<ParkingArea>();

		System.out.println("Query for City: " + city + " State: " + state
				+ " maxPriceFilterEnabled: " + maxPriceFilterEnabled
				+ " MaxPriceFilter: " + +maxPriceFilter
				+ " areaTypeFilterEnabled: " + areaTypeFilterEnabled
				+ " AreaFilter: " + areaFilter);

		Cursor cursor = null;

		if (maxPriceFilterEnabled && areaTypeFilterEnabled) {
			String[] args = { city, state, Double.toString(maxPriceFilter),
					areaFilter.toString() };

			cursor = database
					.rawQuery(
							"select p.area_id, p.area_name, p.address_line, c.city_name, "
									+ "c.city_state, cz.zipcode, p.amount, p.capacity, a.available_spots, p.area_type, p.payment_method, p.phone_number, p.latitude, p.longitude, p.has_position from City c,"
									+ " City_ZipCode cz, Parking_Area p, Availability a where c.city_id = cz.city_id and cz.zipcode_id = p.zipcode_id and"
									+ " p.area_id = a.area_id and c.city_name = ? and   c.city_state = ? and p.amount < ? and p.area_type = ?",
							args);
		} else if (maxPriceFilterEnabled) {
			String[] args = { city, state, Double.toString(maxPriceFilter) };

			cursor = database
					.rawQuery(
							"select p.area_id, p.area_name, p.address_line, c.city_name, "
									+ "c.city_state, cz.zipcode, p.amount, p.capacity, a.available_spots, p.area_type, p.payment_method, p.phone_number, p.latitude, p.longitude, p.has_position from City c,"
									+ " City_ZipCode cz, Parking_Area p, Availability a where c.city_id = cz.city_id and cz.zipcode_id = p.zipcode_id and"
									+ " p.area_id = a.area_id and c.city_name = ? and   c.city_state = ? and p.amount < ?",
							args);

		} else if (areaTypeFilterEnabled) {
			System.out.println("AREA TYPE FILTER " + areaFilter.toString());
			String[] args = { city, state, areaFilter.toString() };

			cursor = database
					.rawQuery(
							"select p.area_id, p.area_name, p.address_line, c.city_name, "
									+ "c.city_state, cz.zipcode, p.amount, p.capacity, a.available_spots, p.area_type, p.payment_method, p.phone_number, p.latitude, p.longitude, p.has_position from City c,"
									+ " City_ZipCode cz, Parking_Area p, Availability a where c.city_id = cz.city_id and cz.zipcode_id = p.zipcode_id and"
									+ " p.area_id = a.area_id and c.city_name = ? and   c.city_state = ? and p.area_type = ?",
							args);

		} else {
			String[] args = { city, state };

			cursor = database
					.rawQuery(
							"select p.area_id, p.area_name, p.address_line, c.city_name, "
									+ "c.city_state, cz.zipcode, p.amount, p.capacity, a.available_spots, p.area_type, p.payment_method, p.phone_number, p.latitude, p.longitude, p.has_position from City c,"
									+ " City_ZipCode cz, Parking_Area p, Availability a where c.city_id = cz.city_id and cz.zipcode_id = p.zipcode_id and"
									+ " p.area_id = a.area_id and c.city_name = ? and   c.city_state = ?",
							args);

		}

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			ParkingArea area = cursorToParkingArea(cursor);
			areas.add(area);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return areas;
	}

	public ArrayList<ParkingArea> getAreasByZipcode(String zipcode,
			boolean maxPriceFilterEnabled, double maxPriceFilter,
			boolean areaTypeFilterEnabled, AreaType areaFilter) {
		ArrayList<ParkingArea> areas = new ArrayList<ParkingArea>();


		
		System.out.println("Query for ZipCode: " + zipcode
				+ " maxPriceFilterEnabled: " + maxPriceFilterEnabled
				+ " MaxPriceFilter: " + +maxPriceFilter
				+ " areaTypeFilterEnabled: " + areaTypeFilterEnabled
				+ " AreaFilter: " + areaFilter);

		Cursor cursor = null;

		if (maxPriceFilterEnabled && areaTypeFilterEnabled) {

			String[] args = { zipcode, Double.toString(maxPriceFilter),
					areaFilter.toString() };
			cursor = database
					.rawQuery(
							"select p.area_id, p.area_name, p.address_line,  c.city_name,"
									+ " c.city_state, cz.zipcode, p.amount, p.capacity, a.available_spots, p.area_type, p.payment_method, p.phone_number, p.latitude, p.longitude, p.has_position"
									+ " from City c, City_ZipCode cz, Parking_Area p, Availability a where c.city_id = cz.city_id "
									+ "and cz.zipcode_id = p.zipcode_id and p.area_id = a.area_id and cz.zipcode = ? and p.amount < ? and p.area_type = ?",
							args);
		} else if (maxPriceFilterEnabled) {

			String[] args = { zipcode, Double.toString(maxPriceFilter) };
			cursor = database
					.rawQuery(
							"select p.area_id, p.area_name, p.address_line,  c.city_name,"
									+ " c.city_state, cz.zipcode, p.amount, p.capacity, a.available_spots, p.area_type, p.payment_method, p.phone_number, p.latitude, p.longitude, p.has_position"
									+ " from City c, City_ZipCode cz, Parking_Area p, Availability a where c.city_id = cz.city_id "
									+ "and cz.zipcode_id = p.zipcode_id and p.area_id = a.area_id and cz.zipcode = ? and p.amount < ?",
							args);

		} else if (areaTypeFilterEnabled) {
			System.out.println("AREA FILTER "+areaFilter.toString());

			String[] args = { zipcode, areaFilter.toString() };
			cursor = database
					.rawQuery(
							"select p.area_id, p.area_name, p.address_line,  c.city_name,"
									+ " c.city_state, cz.zipcode, p.amount, p.capacity, a.available_spots, p.area_type, p.payment_method, p.phone_number, p.latitude, p.longitude, p.has_position"
									+ " from City c, City_ZipCode cz, Parking_Area p, Availability a where c.city_id = cz.city_id "
									+ "and cz.zipcode_id = p.zipcode_id and p.area_id = a.area_id and cz.zipcode = ? and p.area_type=?",
							args);

		} else {

			String[] args = { zipcode };
			cursor = database
					.rawQuery(
							"select p.area_id, p.area_name, p.address_line,  c.city_name,"
									+ " c.city_state, cz.zipcode, p.amount, p.capacity, a.available_spots, p.area_type, p.payment_method, p.phone_number, p.latitude, p.longitude, p.has_position"
									+ " from City c, City_ZipCode cz, Parking_Area p, Availability a where c.city_id = cz.city_id "
									+ "and cz.zipcode_id = p.zipcode_id and p.area_id = a.area_id and cz.zipcode = ?",
							args);

		}

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			ParkingArea area = cursorToParkingArea(cursor);
			areas.add(area);
			cursor.moveToNext();
		}

		// Make sure to close the cursor
		cursor.close();
		return areas;
	}

	private ParkingArea cursorToParkingArea(Cursor cursor) {
		ParkingArea area = new ParkingArea(cursor.getInt(0),
				cursor.getString(1), cursor.getString(2), cursor.getString(3),
				cursor.getString(4), cursor.getString(5), cursor.getFloat(6),
				cursor.getInt(7), cursor.getInt(8), cursor.getString(9),
				cursor.getString(10), cursor.getString(11),
				cursor.getDouble(12), cursor.getDouble(13),
				cursor.getString(14));

		return area;
	}

	public int updateAreaLatLong(int id, double lat, double lon) {
		ContentValues cv = new ContentValues();
		cv.put("latitude", lat);
		cv.put("longitude", lon);
		cv.put("has_position", true);

		return database.update("Parking_Area", cv, "area_id = ?",
				new String[] { id + "" });

	}

}
